import json
from datetime import timedelta
from flask import Flask, render_template, url_for, request, redirect, session

from CheckUtils import CheckUtils
from MyDBUtils import MyDBUtils

app = Flask(__name__)
app.secret_key = "abcdefgh"
app.PERMANENT_SESSION_LIFETIME = timedelta(minutes=30)

# 创建数据库表
def createTable():
    sql1 = 'CREATE TABLE IF NOT EXISTS students(name VARCHAR(30) NOT NULL, ' \
           'uid VARCHAR(30) NOT NULL,school VARCHAR(30) NOT NULL,' \
           'classname VARCHAR(30) NOT NULL,age int NOT NULL,' \
           'phone VARCHAR(11) NOT NULL,' \
           'email VARCHAR(30) NOT NULL,' \
           'user VARCHAR(30) NOT NULL PRIMARY KEY,' \
           'password VARCHAR(30) NOT NULL) character set = utf8;'
    utils1 = MyDBUtils('stu', sql1, ())
    utils1.createTable()
    sql2 = 'CREATE TABLE IF NOT EXISTS `admin`(' \
           'user VARCHAR(30) NOT NULL UNIQUE,' \
           'password VARCHAR(30) NOT NULL) character set = utf8;'
    utils2 = MyDBUtils('stu', sql2, ())
    utils2.createTable()

#将数据添加到数据库
def addToDB(info):
    sqlContent = "INSERT INTO students set name=%s,uid=%s,school=%s,classname=%s,age=%s,phone=%s,email=%s,user=%s,password=%s"
    infos = (info['name'], info['uid'], info['school'],info['classname'], int(info['age']), info['phone'], info['email'],info['user'], info['passwordInfo'])
    utils = MyDBUtils('stu', sqlContent, infos)
    return utils.updateData()

#根目录
@app.route('/')
def index():
    if session.get('user') and session.get('admin'):
        return redirect(url_for('allInfoPage'))
    elif session.get('user'):
        return redirect(url_for('infoPage', user=session['user']))
    else:
        return render_template('login.html')

# 学生注册
@app.route('/register')
def register():
    return render_template('register.html')

#学生登录
@app.route('/login')
def login():
    if session.get('user'):
        user = session['user']
        return render_template("success.html", info={'msg': user+',您已成功登录', 'backUrl': 'infoPage'})
    else:
        return render_template('login.html')

#登出
@app.route('/logout')
def logout():
    if session.get('user'):
        session.pop('user')
    if session.get('admin'):
        session.pop('admin')
    return render_template("success.html", info={'msg': '登出成功', 'backUrl': 'index'})

#管理员登录
@app.route('/admin')
def admin():
    if session.get('user') and session.get('admin'):
        user = session['user']
        return render_template("success.html", info={'msg': user + ',您已成功登录', 'backUrl': 'allInfoPage'})
    else:
        return render_template('admin.html')

#注册信息写入数据库（校验）
@app.route('/registerIntoDB', methods=['POST'])
def registerIntoDB():
    if request.method == 'POST':
        info = request.form
        checkUtils = CheckUtils('register', info)
        result = checkUtils.check()
        if result['code'] == '0':
            return {"code": 0, 'msg': result['msg']}
        sql = "SELECT * FROM students where user=%s"
        utils = MyDBUtils('stu', sql, (info['user'],))
        result = utils.selectData()
        if result:
            return {'code': 0, 'msg': '注册失败,用户已存在'}
        res = addToDB(info)
        if res == "success":
            return {'code': 1, 'msg': '注册成功!'}
        else:
            return {'code': 0, 'msg': '注册失败,系统错误'}

# 校验登录
@app.route('/checkLogin', methods=["POST"])
def checkLogin():
    if request.method == "POST":
        info = request.form
        checkUtils = CheckUtils('login', info)
        result = checkUtils.check()
        if result['code'] == '0':
            return {"code": 0, 'msg': result['msg']}
        sql = 'SELECT `password` FROM students where user=%s'
        utils = MyDBUtils('stu',sql,(info['user']))
        res = utils.selectData()
        if res:
            passwd = res[0][0]
            if passwd == info['passwordInfo']:
                session['user'] = info['user']
                return {"code": 1, "url": 'infoPage', "user": info['user']}
            else:
                return {"code": 0, 'msg': '登录失败，用户名或密码错误'}
        else:
            return {"code": 0, 'msg': '登录失败，用户名或密码错误'}

#学生信息页面
@app.route('/infoPage')
def infoPage():
    if session.get('user'):
        user = session.get('user')
    else:
        return render_template("error.html", info={'msg': '非法访问！用户未登录！', 'backUrl': 'index'})

    sql = "SELECT name '姓名',"\
          "uid '学号',"\
          "school '学院' ,"\
          "classname '班级',"\
          "age '年龄',"\
          "phone '电话',"\
          "email,"\
          "user '用户名' "\
          "FROM students where user=%s"
    utils = MyDBUtils('stu', sql, (user,), 1)
    return render_template("info.html", info=utils.selectData()[0])

# 校验管理员
@app.route("/checkAdmin", methods=['POST'])
def checkAdmin():
    if request.method == 'POST':
        info = request.form
        checkUtils = CheckUtils('login', info)
        result = checkUtils.check()
        if result['code'] == '0':
            return {"code": 0, 'msg': result['msg']}
        sql = "SELECT `password` FROM `admin` where user=%s"
        utils = MyDBUtils('stu', sql, (info['user']))
        res = utils.selectData()
        if res:
            passwd = res[0][0]
            if passwd == info['passwordInfo']:
                session['user'] = info['user']
                session['admin'] = 1
                return {"code": 1, "url": 'allInfoPage'}
            else:
                return {"code": 0, 'msg': '登录失败，用户名或密码错误'}
        else:
            return {"code": 0, 'msg': '登录失败，用户名或密码错误'}

#管理员学生信息管理
@app.route('/allInfoPage')
def allInfoPage(req=None):
    if req is not None:
        return render_template("allInfo.html", infos=req)
    if session.get('admin') == 1:
        sql = "SELECT name '姓名',"\
              "uid '学号',"\
              "school '学院' ,"\
              "classname '班级',"\
              "age '年龄',"\
              "phone '电话',"\
              "email,"\
              "user '用户名' "\
              "FROM students"
        utils = MyDBUtils('stu', sql, (), 1)
        infos = utils.selectData()
        return render_template("allInfo.html", infos=infos)
    else:
        return render_template("error.html", info={'msg': '非法访问！管理员未登录！', 'backUrl': 'index'})

# 删除用户
@app.route('/delStudent', methods=['POST'])
def delStudent():
    if request.method == 'POST':
        user = request.form['user']
        sql = "Delete FROM students WHERE user=%s"
        utils = MyDBUtils('stu', sql, (user,), 1)
        res = utils.updateData()
        if res == 'success':
            return render_template("success.html", info={'msg': '删除成功', 'backUrl': 'allInfoPage'})
        else:
            return render_template("error.html", info={'msg': '删除失败', 'backUrl': 'allInfoPage'})

# 修改用户
@app.route('/editStudent', methods=['POST'])
def editStudent():
    if request.method == 'POST':
        user = request.form['user']
        sql = "SELECT name '姓名',"\
              "uid '学号',"\
              "school '学院' ,"\
              "classname '班级',"\
              "age '年龄',"\
              "phone '电话'," \
              "email," \
              "user '用户名' " \
              "FROM students where user=%s"
        utils = MyDBUtils('stu', sql, (user,), 1)
        result = utils.selectData()
        print(result)
        res = result[0]
        if res:
            return render_template("edit.html", info=res)
        else:
            return render_template("error.html", info={'msg': '访问失败', 'backUrl': 'index'})

# 完成修改写入数据库
@app.route('/finishEdit', methods=['POST','GET'])
def finishEdit():
    if request.method == 'POST':
        info = request.form
        print(info)
        sql = "UPDATE students set name=%s,uid=%s," \
              "school=%s,classname=%s,age=%s,phone=%s," \
              "email=%s,user=%s where user=%s"
        data = (info['姓名'], info['学号'], info['学院'],
                info['班级'], int(info['年龄']), info['电话'], info['email'],
                info['用户名'], info['用户名'])
        utils = MyDBUtils('stu', sql, data)
        res = utils.updateData()
        print(res)
        if res == 'success':
            return render_template("success.html", info={'msg': '修改成功', 'backUrl': 'allInfoPage'})
        else:
            return render_template("error.html", info={'msg': '修改失败', 'backUrl': 'allInfoPage'})
    else:
        return render_template("error.html", info={'msg': '非法访问', 'backUrl': 'index'})

# 查询学生数据
@app.route('/queryData', methods=['POST','GET'])
def queryData():
    if request.method == 'POST':
        req = request.form
        fields = []
        data = ()
        if req.get('name'):
            fields.append('name')
            data = data + (req.get('name'),)
        if req.get('school'):
            fields.append('school')
            data += (req.get('school'),)
        if req.get('classname'):
            fields.append('classname')
            data += (req.get('classname'),)
        if req.get('phone'):
            fields.append('phone')
            data += (req.get('phone'),)
        if req.get('userInfo'):
            fields.append('user')
            data += (req.get('userInfo'),)
        sql = "SELECT name 姓名,uid 学号,school 学校,classname 班级,age 年龄,phone 电话,email,user 用户名 FROM students WHERE "
        for i in range(len(fields)):
            if i == len(fields)-1:
                sql += fields[i] + "=%s;"
            else:
                sql += fields[i] + "=%s and "
        utils = MyDBUtils('stu', sql, data, 1)
        res = utils.selectData()
        if res == 'fail':
            return redirect("allInfoPage")
        print(res)
        return allInfoPage(res)
    else:
        return redirect("allInfoPage")

# 数据可视化展示
@app.route('/showData')
def showData():
    if not session.get('admin'):
        return render_template("error.html", info={'msg': '非法访问', 'backUrl': 'index'})

    lineData = {"code": 0, "result": True, "messge": "success",
                "data": {"xAxis": [{"type": "category", "data": []}],
                         "series": [{"name": "人数", "type": "bar", "data": []},
                                    {"name": "平均年龄", "type": "bar", "data": []}]}
               }
    pieData = {"code": 0, "result": True, "messge": "success",
               "data": {"title": "各班级人数统计",
                        "series": []}
               }
    studentCount = 0
    schoolForMax = ''
    classForMax = ''
    schoolList = []
    schoolData = []
    avgAgeData = []

    utils1 = MyDBUtils('stu', "SELECT COUNT(*) FROM students;", ())
    results1 = utils1.selectData()
    studentCount = results1[0][0]
    utils2 = MyDBUtils('stu', "SELECT school,COUNT(*),AVG(age) FROM students GROUP BY school ORDER BY COUNT(*) DESC;", ())
    results2 = utils2.selectData()
    schoolForMax = results2[0][0]
    for content in results2:
        schoolList.append(content[0])
        schoolData.append(content[1])
        avgAgeData.append(int(content[2]))
    lineData["data"]["xAxis"][0]["data"] = schoolList
    lineData["data"]["series"][0]["data"] = schoolData
    lineData["data"]["series"][1]["data"] = avgAgeData
    lineData = json.dumps(lineData)
    utils3 = MyDBUtils('stu', "SELECT classname,COUNT(*) FROM students GROUP BY classname ORDER BY COUNT(*) DESC;", ())
    classData = utils3.selectData()
    for content in classData:
        pieData["data"]["series"].append({"name": content[0], "value": content[1]})
    pieData = json.dumps(pieData)
    writeToJson(lineData, pieData)
    classForMax = classData[0][0]
    return render_template('dataAnalyse.html', studentCount=studentCount, schoolForMax=schoolForMax,
                           classForMax=classForMax, barData=lineData)

# 将数据写入json文件 以供图表使用
def writeToJson(lineData, pieData):
    with open('./static/dataLine.json', 'w', encoding='utf-8') as file:
        file.write(lineData)
    with open('./static/dataPie.json', 'w', encoding='utf-8') as file:
        file.write(pieData)

if __name__ == '__main__':
    createTable()
    app.run(debug=True)
